package com.supermap.dataservice.cloud.china;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @version v1.0.0
 * @Author:zhoujian
 * @Desciption:综合统计
 * @date 2019-05-07 15:17
 */
@Mapper
public interface SyntheticalStatisticsMapper {

    @Select(" /*专家*/" +
            "select  te.areaname , expert_count , user_count , advisory_count, fland_count,speciality_product_count ,site_user_count ,agri_count\n" +
            "             from \n" +
            "            (select a.areacode ,if(a.areaname=\"重庆市\",\"科研所\",a.areaname) as areaname ,  count(t1.id) as expert_count from t_area a left join \n" +
            "            (\n" +
            "            select t.id, u.areaCode , t.createtime from t_expertinfo t inner join t_user u on t.userid = u.id where u.del_flg= 0 and t.del_flg = 0 and t.createtime between #{startDate} and #{endDate}\n" +
            "            )t1 on a.areacode = t1.areaCode\n" +
            "            where length(a.areacode) <= 4 and a.areacode like concat( #{areaCode},\"%\")  group by  a.areaid ) te,\n" +
            "            (\n" +
            "            /*用户*/\n" +
            "            select if(a.areaname=\"重庆市\",\"科研所\",a.areaname) as areaname ,  count(t1.id) as user_count from t_area a \n" +
            "            left join (select u.id,u.areaCode from  t_user u where u.del_flg = 0 and u.createtime between #{startDate} and #{endDate}) t1  on t1.areaCode = a.areacode \n" +
            "            where length(a.areacode) <= 4 and a.areaCode like concat(#{areaCode},\"%\")  group by a.areaid\n" +
            "            )tu ,\n" +
            "            (\n" +
            "            /*咨询数量*/\n" +
            "            SELECT IF(a.areaname=\"重庆市\",\"科研所\",a.areaname) AS areaname, COUNT(t1.id) AS advisory_count\n" +
            "            FROM t_area a\n" +
            "            LEFT JOIN (\n" +
            "            SELECT t.id, t.areacode\n" +
            "            FROM t_advisoryinfo t\n" +
            "            WHERE t.uploadTime BETWEEN #{startDate} and #{endDate}) t1 ON t1.areacode = a.areacode\n" +
            "            WHERE length(a.areacode) <= 4 and a.areacode like concat( #{areaCode},\"%\")\n" +
            "            GROUP BY a.areaid\n" +
            "            ) ta,\n" +
            "            (\n" +
            "            SELECT if(a.areaname=\"重庆市\",\"科研所\",a.areaname) as areaname, COUNT(t1.id) AS fland_count\n" +
            "            FROM t_area a\n" +
            "            LEFT JOIN (select t.id,t.code from t_farmland t where t.createtime BETWEEN #{startDate} and #{endDate}) t1 ON a.areacode = t1.code\n" +
            "            WHERE length(a.areacode) <= 4 and a.areacode like concat( #{areaCode},\"%\") \n" +
            "            GROUP BY a.areaid\n" +
            "            ) tf,\n" +
            "            (\n" +
            "            /*专业产品数量*/\n" +
            "            SELECT IF(a.areaname='重庆市','科研所',a.areaname) AS areaname, COUNT(t1.id) AS speciality_product_count\n" +
            "            FROM t_area a\n" +
            "            LEFT JOIN (\n" +
            "            SELECT t.id, t.areacode\n" +
            "            FROM t_area_product t\n" +
            "            WHERE t.createtime BETWEEN #{startDate} and #{endDate}) t1 ON a.areacode = t1.areacode\n" +
            "            WHERE length(a.areacode) <= 4 and a.areacode like concat( #{areaCode},\"%\")\n" +
            "            GROUP BY a.areaid\n" +
            "            ) tap,\n" +
            "            (\n" +
            "            /*加法*/\n" +
            "            /*分区统计建站用户数量*/\n" +
            "            select tt1.areaname , tt1.counts+tt2.counts  as site_user_count from \n" +
            "            (\n" +
            "            select IF(a.areaname='重庆市','科研所',a.areaname) AS areaname , count(t1.id) as counts from t_area a left join \n" +
            "            (\n" +
            "            select t.id , u.areaCode from  t_company t \n" +
            "            inner join t_user u on t.userid = u.id \n" +
            "            where t.url is not null\n" +
            "            and t.createtime between #{startDate} and #{endDate} \n" +
            "            ) t1 on a.areacode = t1.areaCode\n" +
            "            where length(a.areacode) <= 4 and a.areacode like concat( #{areaCode},\"%\")\n" +
            "            group by a.areaid \n" +
            "            ) tt1,\n" +
            "            (\n" +
            "            select IF(a.areaname='重庆市','科研所',a.areaname) AS areaname ,count(t2.id) as counts from t_area a left join \n" +
            "            (\n" +
            "            select t.id , u.areaCode from  t_countryside t \n" +
            "            inner join t_user u on t.userid = u.id \n" +
            "            where t.url is not null\n" +
            "            and t.createtime between #{startDate} and #{endDate} \n" +
            "            ) t2 on a.areacode = t2.areaCode\n" +
            "            where length(a.areacode) <= 4 and a.areacode like concat( #{areaCode},\"%\")\n" +
            "            group by a.areaid\n" +
            "            ) tt2 \n" +
            "            where tt1.areaname = tt2.areaname\n" +
            "            ) tc,\n" +
            "            (\n" +
            "            /*区域分组统计农情数量*/\n" +
            "            SELECT IF(a.areaname='重庆市','科研所',a.areaname) AS areaname , COUNT(t1.id) AS agri_count\n" +
            "            FROM t_area a\n" +
            "            LEFT JOIN (\n" +
            "            SELECT t.id, t.areacode\n" +
            "            FROM t_agrinfo t\n" +
            "            WHERE t.createtime BETWEEN #{startDate} and #{endDate}) t1 ON a.areacode = t1.areacode\n" +
            "            WHERE length(a.areacode) <= 4 and a.areacode like concat( #{areaCode},\"%\")\n" +
            "            GROUP BY a.areaid\n" +
            "            ) tag\n" +
            "            where te.areaname = tu.areaname\n" +
            "            and te.areaname = ta.areaname\n" +
            "            and te.areaname = tf.areaname\n" +
            "            and te.areaname = tap.areaname\n" +
            "            and te.areaname = tc.areaname\n" +
            "            and te.areaname = tag.areaname\n" +
            "            order by te.areacode")
    List<Map<String,Object>> SyntheticalStatistics(@Param("areaCode") String areaCode , @Param("startDate") String startDate , @Param("endDate") String endDate);


    @Select("SELECT t1.areaname, expert_product_count + expert_product_count1 AS expert_product_count\n" +
            "FROM \n" +
            " (\n" +
            " \t /*二期*/\n" +
            "SELECT IF(a.areaname=\"重庆市\",\"科研所\",a.areaname) AS areaname, COUNT(ts.id) AS expert_product_count\n" +
            "FROM t_area a\n" +
            "LEFT JOIN \n" +
            "\t (\n" +
            "SELECT s.id, s.areacode\n" +
            "FROM t_suggest s\n" +
            "WHERE s.stime BETWEEN #{startDate} AND #{endDate}) ts ON a.areacode = ts.areacode\n" +
            "WHERE LENGTH(a.areacode) <= 4 AND a.areacode LIKE concat( #{areaCode},\"%\")\n" +
            "GROUP BY a.areaid\n" +
            "\t\t\t\t) t1,\n" +
            "\t\t\t\t (/*一期*/\n" +
            "SELECT IF(a.areaname=\"重庆市\",\"科研所\",a.areaname) AS areaname, COUNT(ts.productID) AS expert_product_count1\n" +
            "FROM t_area a\n" +
            "LEFT JOIN \n" +
            "\t (\n" +
            "SELECT s.productID, s.areacode\n" +
            "FROM t_expertproduct s\n" +
            "WHERE s.createTime BETWEEN #{startDate} AND #{endDate}) ts ON a.areacode = ts.areacode\n" +
            "WHERE LENGTH(a.areacode) <= 4 AND a.areacode LIKE concat( #{areaCode},\"%\")\n" +
            "GROUP BY a.areaid\n" +
            "\t\t\t\t) t2\n" +
            "WHERE t1.areaname = t2.areaname")
    List<Map<String , Object>> syntheticalStatisticsByExpertProduct(@Param("areaCode") String areaCode , @Param("startDate") String startDate , @Param("endDate") String endDate);



    @Select("select tx.areaname , ad_count+ag_count+su_count as active_expert_count from \n" +
            "            (\n" +
            "            /*统计专家咨询评论*/\n" +
            "            select if(a.areaname = \"重庆市\", \"科研所\",a.areaname) as areaname, count(ttt.id) as ad_count from t_area a left join \n" +
            "            (\n" +
            "            select u.id , u.areaCode from t_expertinfo tx  inner join t_advinfocomment t \n" +
            "            on tx.userid = t.userId\n" +
            "            inner join t_user u \n" +
            "            on tx.userid = u.id\n" +
            "            where t.commentTime between #{startDate} and #{endDate}\n" +
            "            ) ttt\n" +
            "            on a.areacode = ttt.areaCode\n" +
            "            where length(a.areacode) <= 4 and a.areacode like concat( #{areaCode},\"%\")\n" +
            "            group by a.areaid\n" +
            "            ) tx inner join \n" +
            "            (\n" +
            "            /*统计专家农情评论*/\n" +
            "            select if(a.areaname = \"重庆市\", \"科研所\",a.areaname) as areaname, count(ttt.id) as ag_count from t_area a left join \n" +
            "            (\n" +
            "            select u.id , u.areaCode from t_expertinfo tx  inner join t_agrinfocomment t \n" +
            "            on tx.userid = t.userId\n" +
            "            inner join t_user u \n" +
            "            on tx.userid = u.id\n" +
            "            where t.commentTime between #{startDate} and #{endDate}\n" +
            "            ) ttt\n" +
            "            on a.areacode = ttt.areaCode\n" +
            "            where length(a.areacode) <= 4 and a.areacode like concat( #{areaCode},\"%\")\n" +
            "            group by a.areaid\n" +
            "            ) tx2 on tx.areaname = tx2.areaname\n" +
            "            inner join \n" +
            "            (\n" +
            "            /*专家建议*/\n" +
            "            select if(a.areaname=\"重庆市\",\"科研所\",a.areaname ) as areaname, count(ts.id) as su_count from t_area a left join \n" +
            "            (select s.id , s.areacode from t_suggest s where s.stime between #{startDate} and #{endDate}) ts on a.areacode = ts.areacode\n" +
            "            where length(a.areacode) <= 4 and a.areacode  like concat( #{areaCode},\"%\")\n" +
            "            group by a.areaid\n" +
            "            ) tx3 on tx2.areaname = tx3.areaname")
    List<Map<String , Object>> syntheticalStatisticsByActiveExpert(@Param("areaCode") String areaCode , @Param("startDate") String startDate , @Param("endDate") String endDate);





    @Select("SELECT IF(t4.areaname='重庆市','科研所',t4.areaname) AS areaname, COUNT(t5.id) AS public_product_count\n" +
            "FROM t_area t4\n" +
            "LEFT JOIN (\n" +
            "select tt.id, t3.depareacode,tt.create_date , tt.product_maker from (\n" +
            "\tSELECT t1.id,  t1.create_date , t1.product_maker\n" +
            "\tFROM t_b_pms_product t1 where  t1.product_state = 6 and t1.create_date BETWEEN #{startDate} AND #{endDate}) tt\n" +
            "INNER JOIN t_s_user_org t2 ON tt.product_maker = t2.user_id\n" +
            "INNER JOIN t_s_depart t3 ON t2.org_id = t3.ID)t5 ON t4.areacode = t5.depareacode\n" +
            "WHERE length(t4.areacode) <= 4 and t4.areacode LIKE CONCAT(#{areaCode},\"%\")\n" +
            "GROUP BY t4.areaid order by t4.areacode")
    List<Map<String,Object>> publicProductStatistics(@Param("areaCode") String areaCode , @Param("startDate") String startDate , @Param("endDate") String endDate);




}